import sys
sys.path.extend(["..//database", "..//futuredata"])
from SqlWizard import *
from spec_class import Spec
from CmnFuncs import *

import tushare as ts
pro = ts.pro_api()
import akshare as ak
import pandas as pd
import numpy as np
from scipy.stats import skew, kurtosis, shapiro
from pyecharts.charts import Bar, Page, Scatter, Scatter3D, HeatMap
from pyecharts.components import Table
import pyecharts.options as opts
from pyecharts.commons import utils
from pyecharts.globals import ThemeType


def get_today_sentiment(namelist, dateidx):
    '''获取品种给定日期的市场一致预期指数'''
    listdex, listdate, nlist, ave, upper, lower = [], [], [], [], [], []
    for name in namelist:
        spec = Spec(name)
        symb = spec.symbol
        sym = symb.lower()
        df = read_sql_spdrdx("spdrdex_{}".format(sym))
        if df.empty:
            continue
        else:
            date = df.index.to_list()[dateidx]
            informed = df['informed'].to_list()
            uninformed = df['uninformed'].to_list()
            np.seterr(divide='ignore', invalid='ignore', )
            senti = np.multiply(np.array(informed).astype(np.float), np.array(uninformed).astype(np.float))

            sentix = senti[np.logical_not(np.isnan(senti))]
            if len(sentix) != 0:
                sentiaver = float(np.nanmean(sentix))
                sentistd = float(np.nanstd(sentix))
                sentiupper = sentiaver + sentistd * 2
                sentilower = sentiaver - sentistd * 2
            else:
                sentiaver = 0
                sentiupper = 0
                sentilower = 0
            ave.append(sentiaver)
            upper.append(sentiupper)
            lower.append(sentilower)
            sentilist = senti.tolist()
            dex = sentilist[dateidx]
            listdex.append(dex)
            listdate.append(date)
            nlist.append(name)

    seri = pd.Series(nlist, index=listdex)
    seri.sort_index(ascending=False, inplace=True)
    if len(set(listdate)) <= 2:
        datestamp = max(set(listdate))
        bar = (
            Bar(init_opts=opts.InitOpts(theme=ThemeType.SHINE))
            .add_xaxis(seri.to_list())
            .add_yaxis('市场一致性预期', seri.index.to_list())
            .set_global_opts(title_opts=opts.TitleOpts(title="当天全品类一致预期指数",
                                                       title_textstyle_opts=opts.TextStyleOpts(font_weight='bold'),
                                                       subtitle="{}".format(datestamp)),
                             yaxis_opts=opts.AxisOpts(is_scale=True), toolbox_opts=opts.ToolboxOpts(),
                             xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=-45)),
                             tooltip_opts=opts.TooltipOpts(trigger='axis'))
            .set_series_opts(label_opts=opts.LabelOpts(is_show=False))
        )
        sca = (
            Scatter()
            .add_xaxis(seri.to_list())
            .add_yaxis('Average', ave)
            .add_yaxis('+2SD', upper)
            .add_yaxis('-2SD', lower)
            .set_series_opts(label_opts=opts.LabelOpts(is_show=False))
        )
        bar.overlap(sca)
    else:
        datestamp = False
        bar = (
            Bar()
            .set_global_opts(title_opts=opts.TitleOpts(title="{}".format(set(listdate))))
        )
    bar.width = '1600px'
    bar.height = '360px'
    return bar, datestamp


def get_hld_bar(namelist, date, cat=0):
    if date:
        df1 = pro.fut_holding(trade_date=date, exchange='SHFE')
        df2 = pro.fut_holding(trade_date=date, exchange='DCE')
        df3 = pro.fut_holding(trade_date=date, exchange='CZCE')
        df = pd.concat([df1, df2, df3])
        df = df.fillna(0)
        df.eval("net_hld = long_hld - short_hld", inplace=True)
        df.eval("net_chg = long_chg - short_chg", inplace=True)
        df.set_index('symbol', inplace=True)
        tothld, totchg = [], []
        for name in namelist:
            spec = Spec(name)
            sym = spec.symbol
            contracts = get_iter_num(name)
            if len(contracts) > 1:
                contract = contracts[cat]
            else:
                clist = []
                for e in df.index.to_list():
                    if sym in e and len(e.strip(sym)) == 4:
                        try:
                            clist.append(int(e.strip(sym)))
                        except Exception as err:
                            print("Holding_Bar Error: {}".format(err))
                        else:
                            try:
                                contract = str(sorted(clist)[cat])
                            except Exception as er:
                                print('Holding_Bar Contracts Error:{}--{}'.format(name, er))
                            else:
                                pass
            code = spec.get_code(contract)
            try:
                fc = df.loc[code].copy()
            except Exception as error:
                tot_nethld = 0
                tot_netchg = 0
                print('Holding_Bar Error {}'.format(error))
            else:
                tot_nethld = fc['net_hld'].sum()
                tot_netchg = fc['net_chg'].sum()
            tothld.append(tot_nethld)
            totchg.append(tot_netchg)
        np.seterr(divide='ignore', invalid='ignore')
        ths = np.divide(np.array(tothld), np.abs(np.array(tothld)))
        tcs = np.divide(np.array(totchg), np.abs(np.array(totchg)))
        tth = np.log(np.abs(np.array(tothld)))
        tothld = np.multiply(tth, ths)
        ttc = np.log(np.abs(np.array(totchg)))
        totchg = np.multiply(ttc, tcs)
        dfb = pd.DataFrame(data=[tothld, totchg], columns=namelist, index=['tot_nethld', 'tot_netchg'])
        dfb = dfb.T
        dfb.sort_values(by=['tot_nethld', 'tot_netchg'], ascending=(False, False), inplace=True)
        bar = (
            Bar()
            .add_xaxis(dfb.index.to_list())
            .add_yaxis('净持仓', dfb['tot_nethld'].to_list(), stack='stack1')
            .add_yaxis('净仓位变化', dfb['tot_netchg'].to_list(), stack='stack1')
            .set_global_opts(title_opts=opts.TitleOpts(title="全品类对数{}净持仓变化图".format('主力' if cat == 0 else '次主力'),
                                                       subtitle="{}".format(date)),
                             toolbox_opts=opts.ToolboxOpts(), yaxis_opts=opts.AxisOpts(is_scale=True),
                             xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=-45)),
                             tooltip_opts=opts.TooltipOpts(trigger='axis'))
            .set_series_opts(label_opts=opts.LabelOpts(is_show=False))
        )
    else:
        bar = (
            Bar()
            .set_global_opts(title_opts=opts.TitleOpts(title="DateStamp Error"))
        )

    bar.width = '1600px'
    bar.height = '360px'

    return bar


def nh_volitility_index(namelist, period=20):
    '''南华商品波动率指数，默认前20日波动率的历史比较'''
    volilist, volipos, averlist, uplist, lowerlist, nlist, datelist = [], [], [], [], [], [], []
    for name in namelist:
        spec = Spec(name)
        sym = spec.symbol
        try_count = 0
        while True:
            try:
                nhvl = ak.nh_volatility_index(code=sym, day_count=period)
                # print(name, '\n', nhvl, '\n', type(nhvl))
            except Exception as error:
                try_count += 1
                if try_count > 10:
                    print("NH volitility index Fetch Error: {}--{}".format(sym, error))
                    break
                else:
                    continue
            else:
                break
        if nhvl is not None and len(nhvl['value'].to_list()) >= 10:
            datelist.append(nhvl.index.to_list()[-1])
            volist = nhvl['value'].to_list()
            curr_voli = volist[-1]
            lesslist = np.array(volist)[np.array(volist) <= curr_voli].tolist()
            curr_pos = round(((len(lesslist) / len(volist)) * 100), 2)
            ave = np.nanmean(np.array(volist))
            upper = ave + np.nanstd(np.array(volist))
            lower = ave - np.nanstd(np.array(volist))
            volilist.append(curr_voli)
            volipos.append(curr_pos)
            averlist.append(ave)
            uplist.append(upper)
            lowerlist.append(lower)
            nlist.append(name)
        else:
            continue

    if len(set(datelist)) == 1:
        bar = (
            Bar(init_opts=opts.InitOpts(theme=ThemeType.SHINE))
            .add_xaxis(nlist)
            .add_yaxis('{}日波动率'.format(period), volilist)
            .extend_axis(yaxis=opts.AxisOpts(is_scale=True))
            .add_yaxis('{}日波动率历史水平'.format(period), volipos, yaxis_index=1,
                       itemstyle_opts=opts.ItemStyleOpts(color='rgb(54, 54, 54)'))
            .set_global_opts(title_opts=opts.TitleOpts(title="{}日波动率描述".format(period),
                                                       title_textstyle_opts=opts.TextStyleOpts(font_weight='bold'),
                                                       subtitle="{}".format(set(datelist))),
                             yaxis_opts=opts.AxisOpts(is_scale=True), toolbox_opts=opts.ToolboxOpts(),
                             xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=-45)),
                             tooltip_opts=opts.TooltipOpts(trigger='axis'))
            .set_series_opts(label_opts=opts.LabelOpts(is_show=False))
        )
        sca = (
            Scatter()
            .add_xaxis(nlist)
            .add_yaxis('Average', averlist)
            .add_yaxis('+SD', uplist)
            .add_yaxis('-SD', lowerlist)
            .set_series_opts(label_opts=opts.LabelOpts(is_show=False))
        )
        bar.overlap(sca)
    else:
        bar = (
            Bar()
            .set_global_opts(title_opts=opts.TitleOpts(title="{}".format(set(datelist))))
        )
    bar.width = '1600px'
    bar.height = '360px'
    return bar


def gator_sensor(name, speclist, date, cat=0):
    '''获取头部机构每日的持仓信息'''
    if date:
        df1 = pro.fut_holding(trade_date=date, exchange='SHFE')
        df2 = pro.fut_holding(trade_date=date, exchange='DCE')
        df3 = pro.fut_holding(trade_date=date, exchange='CZCE')
        df = pd.concat([df1, df2, df3])
        dfn = df[df['symbol'].str.len() > 4]
        dfn.set_index('broker', inplace=True)
        fc = dfn.loc[name].copy()
        fc.set_index('symbol', inplace=True)
        trailist = []
        for n in speclist:
            spec = Spec(n)
            sym = spec.symbol
            contracts = get_iter_num(n)
            if len(contracts) > 1:
                contract = contracts[cat]
            else:
                clist = []
                for e in fc.index.to_list():
                    if sym in e and len(e.strip(sym)) == 4:
                        # 这里主要是判断，如果contracts只有L，那么就应该在拿到的持仓数据中按照时间排序去拿合约编号，也就是近月合约
                        try:
                            clist.append(int(e.strip(sym)))
                        except Exception as err:
                            print("Gator_Sensor Error:{}--{}".format(name, err))
                        else:
                            try:
                                contract = str(sorted(clist)[cat])
                            except Exception as er:
                                print('Gator_Sensor Contracts Error:{}--{}{}'.format(name, n, er))
            try:
                code = spec.get_code(contract)
                trail = fc.loc[code].copy()
            except Exception as error:
                print("Gator_Sensor Error:{}--{}".format(name, error))
            else:
                trailist.append(trail)
        fc = pd.DataFrame(data=trailist)
        fc = fc.fillna(0)
        fc.eval("net_hld = long_hld - short_hld", inplace=True)
        fc.eval("net_chg = long_chg - short_chg", inplace=True)
        fc.sort_values('net_hld', ascending=False, inplace=True)
        xlist = fc.index.to_list()
        ylist1 = fc['net_hld'].to_list()
        ylist2 = fc['net_chg'].to_list()

        gator_df = pd.DataFrame([xlist, ylist1, ylist2], index=['symbol', 'nthd', 'ntcg']).T
        gator_df.set_index('symbol', inplace=True)

        bar = (
            Bar()
            .add_xaxis(xlist)
            .add_yaxis('净持仓', ylist1, stack='stack1')
            .add_yaxis('净仓位变化', ylist2, stack='stack1')
            .reversal_axis()
            .set_global_opts(title_opts=opts.TitleOpts(title="{}{}".format(name, '主力' if cat == 0 else '次主力'),
                                                       subtitle="{}".format(date)),
                             yaxis_opts=opts.AxisOpts(is_scale=True), toolbox_opts=opts.ToolboxOpts(),
                             xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=-45)),
                             tooltip_opts=opts.TooltipOpts(trigger='axis'))
            .set_series_opts(label_opts=opts.LabelOpts(is_show=False))
        )
    else:
        bar = (
            Bar()
            .set_global_opts(title_opts=opts.TitleOpts(title="{}Error".format(name)))
        )
        gator_df = False

    bar.width = '500px'
    bar.height = '800px'
    return bar, gator_df


def gator_forest(dflist, cat=0):
    # 这里相当于将每个机构的持仓当作一个决策树，再汇总到这里进行投票决策。
    df = pd.concat(dflist, axis=1, sort=False)
    dfhd = df['nthd'].copy()
    dfcg = df['ntcg'].copy()
    hd_sample_size = dfhd.count(axis=1)
    hd_sample = hd_sample_size[hd_sample_size >= 5]
    hd_list = hd_sample.index.to_list()
    cg_sample_size = dfcg.count(axis=1)
    cg_sample = cg_sample_size[cg_sample_size >= 5]
    cg_list = cg_sample.index.to_list()
    sample_list = list(set(hd_list).intersection(set(cg_list)))
    outcome = []
    dfhd.fillna(0, inplace=True)
    dfcg.fillna(0, inplace=True)
    for s in sample_list:
        hd_trail = dfhd.loc[s]
        hd_bulls = len(hd_trail[hd_trail > 0])
        hd_bears = len(hd_trail[hd_trail < 0])
        if hd_bulls > hd_bears:
            hd_bb = 1
        elif hd_bulls == hd_bears:
            hd_bb = 0
        else:
            hd_bb = -1
        hd_sum = hd_trail.sum()
        if hd_bb*hd_sum > 0:
            hd = hd_bb
        elif hd_bb*hd_sum == 0 and hd_sum != 0:
            hd = hd_sum/abs(hd_sum)
        else:
            hd = 0
        cg_trail = dfcg.loc[s]
        cg_bulls = len(cg_trail[cg_trail > 0])
        cg_bears = len(cg_trail[cg_trail < 0])
        if cg_bulls > cg_bears:
            cg_bb = 1
        elif cg_bulls == cg_bears:
            cg_bb = 0
        else:
            cg_bb = -1
        cg_sum = cg_trail.sum()
        if cg_bb*cg_sum > 0:
            cg = cg_bb
        elif cg_bb*cg_sum == 0 and cg_sum != 0:
            cg = cg_sum/abs(cg_sum)
        else:
            cg = 0

        if cg*hd > 0:
            result = hd
        elif cg*hd < 0:
            result = hd/2
        else:
            result = 0
        outcome.append(result)

    index_list = [i.rstrip(i[-4:]) for i in sample_list]
    sort_data = pd.Series(data=outcome, index=index_list)
    sort_data.name = '{}'.format('Main' if cat == 0 else 'Sub_Main')

    return sort_data


def gator_map(seri1, seri2, date):
    df = pd.DataFrame(data=[seri1, seri2]).T
    df.sort_values(by=['Main', 'Sub_Main'], ascending=(False, False), inplace=True)

    bar = (
        Bar()
        .add_xaxis(df.index.to_list())
        .add_yaxis("Main_Bias", df['Main'].to_list())
        .add_yaxis("Sub_Bias", df['Sub_Main'].to_list())
        .set_global_opts(title_opts=opts.TitleOpts(title="当日牛熊指数",
                                                   subtitle="{}".format(date)),
                         yaxis_opts=opts.AxisOpts(is_scale=True), toolbox_opts=opts.ToolboxOpts(),
                         xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=-45)),
                         tooltip_opts=opts.TooltipOpts(trigger='axis'))
        .set_series_opts(label_opts=opts.LabelOpts(is_show=False))
    )

    bar.width = '1600PX'
    bar.height = '360PX'

    return bar



def run_skew(namelist, datestamp):
    skewnum = 20
    skewdict, kvaldict, stddict, pdict, symdict = {}, {}, {}, {}, {}
    nlist = []
    throwlist = []
    for name in namelist:
        spec = Spec(name)
        symbol = spec.symbol
        df = read_sql_fut_index("fut_index_{}".format(symbol.lower()))
        if len(df.index.to_list()) < skewnum:
            continue
        elif df.index.to_list()[-1] == datestamp:
            close = df['close'].to_list()
            lnclose = np.log(np.array(close)).tolist()
            closeperc = pd.Series(lnclose).pct_change()
            returns = closeperc.to_list()[-skewnum:]
            np.seterr(divide='ignore', invalid='ignore')
            clearreturns = np.array(returns)[np.logical_not(np.isnan(np.array(returns)))]
            if len(clearreturns.tolist()) <= int(skewnum*0.9):
                continue
            else:
                std = np.std(clearreturns)
                clearreturnlist = clearreturns.tolist()
                skewness = skew(clearreturnlist)
                kvalue = kurtosis(clearreturnlist)  # 相对正态分布峰度的超值峰度 即fisher默认为True，如果正态分布则返回0
                shap = shapiro(clearreturnlist)
                pvalue = round(shap[1], 3)

                closesample = np.array(close)[np.logical_not(np.isnan(np.array(close)))].tolist()[-skewnum:]
                totalreturn = round((((closesample[-1] - closesample[0]) / closesample[0]) * 100), 2)
                flucrange = round(((max(closesample) - min(closesample)) / min(closesample) * 100), 2)
                throwdict = {'name': name, 'ttre': totalreturn, 'flcrange': flucrange}
                throwlist.append(throwdict)

                if pvalue > 0:
                    skewdict[name] = skewness
                    kvaldict[name] = kvalue
                    stddict[name] = std
                    pdict[name] = pvalue
                    symdict[name] = symbol
                    nlist.append(name)
                else:
                    continue

        else:
            continue

    # 准备抛给表格的数据：
    throwdf = pd.DataFrame(throwlist)
    # 构造数据
    datasource = [
        [
            skewdict[n],
            stddict[n],
            kvaldict[n],
            pdict[n],
            symdict[n]
        ]
        for n in nlist
    ]
    skewlis = list(skewdict.values())
    max_skew = max(skewlis) * 1.1
    min_skew = min(skewlis) * 1.1
    rc = ['#313695', '#4575b4', '#d73027', '#a50026']
    # label_formattype = "function(params){return params.value[4];}"
    tooltip_formattype = "function(params){return params.value[4];}"
    #  + ' : ' + params.value[4]
    scatter = (
        Scatter3D()
        .add("", data=datasource,
             xaxis3d_opts=opts.Axis3DOpts(type_="value", name='Skewness'),
             yaxis3d_opts=opts.Axis3DOpts(type_="value", name='STD on Returns'),
             zaxis3d_opts=opts.Axis3DOpts(type_="value", name='Relative Kurtosis'),
             grid3d_opts=opts.Grid3DOpts(is_rotate=True, rotate_speed=5)
             )
        .set_global_opts(title_opts=opts.TitleOpts(title="前{}日市场收益率描述".format(skewnum), subtitle=datestamp),
                         visualmap_opts=opts.VisualMapOpts(pos_left='left', range_color=rc, dimension=0,
                                                           min_=min_skew, max_=max_skew),
                         tooltip_opts=opts.TooltipOpts(),
                         toolbox_opts=opts.ToolboxOpts()
                         )
        .set_series_opts(label_opts=opts.LabelOpts(formatter=utils.JsCode(tooltip_formattype)))

    )

    scatter.width = '1000px'
    scatter.height = '600px'
    return scatter, throwdf, skewnum


def calc_hurst(closelist):
    lnclose = np.log(np.array(closelist)).tolist()
    diffini = pd.Series(lnclose).pct_change().to_list()[1:]
    diff = np.abs(np.array(diffini)).tolist()
    marklist, nlist = [], []
    rang = len(closelist)
    for n in range(2, (rang//2)+1):
        subi = len(diff)//n  # 将diff等分为n份， 每份子集长度为subi
        sublist, disperslist, dbpds = [], [], []
        for i in range(n):
            subdiff = diff[i*subi: (i+1)*subi]  # n个整段的子集
            avesubdiff = np.nanmean(np.array(subdiff))   # 对每个整段子集求均值
            subdispers = np.array(subdiff) - avesubdiff  # 离差列表
            subdispersaccu = subdispers.cumsum()  # 子集累计离差列表
            # 每个子集内对数收益率序列的波动范围Ra
            ra = max(subdispersaccu) - min(subdispersaccu)
            substd = np.nanstd(np.array(subdiff))  # 对该子集求对数收益率序列的标准差
            dbpd = ra / substd
            if np.isnan(dbpd):
                pass
            else:
                dbpds.append(dbpd)
            sublist += subdiff
        subrest = [sr for sr in diff if sr not in sublist]  # 取完整段之后，余下的尾巴子集
        if len(subrest) != 0:
            avesubrest = np.nanmean(np.array(subrest))  # 余下的尾巴子集求均值
            subdisperstail = np.array(subrest) - avesubrest  # 尾巴子集离差列表
            subdispersaccutail = subdisperstail.cumsum()  # 尾巴子集累计离差列表
            ratail = max(subdispersaccutail) - min(subdispersaccutail)
            substdtail = np.nanstd(np.array(subrest))  # 对尾巴子集求对数收益率序列的标准差
            dbpdtail = ratail / substdtail
            if np.isnan(dbpdtail):
                pass
            else:
                dbpds.append(dbpdtail)
        else:
            pass
        mark_n = np.nanmean(np.array(dbpds))
        if np.isnan(mark_n):
            pass
        else:
            ln_mark = np.log(mark_n)
            ln_n = np.log(n)
            marklist.append(ln_mark)
            nlist.append(ln_n)
    if len(nlist) == 0 or len(marklist) == 0:
        hurst = None
    else:
        corres = np.polyfit(np.array(marklist), np.array(nlist), deg=1)[0]
        hurst = np.sqrt(corres**2/(1+corres**2))

    return hurst


def run_hurst(namelist, datestamp):
    hurnum = 20
    resultdict = {}
    diflvlist, hurlist, symlist = [], [], []
    for name in namelist:
        spec = Spec(name)
        symb = spec.symbol
        dff = read_sql_fut_index("fut_index_{}".format(symb.lower()))
        if len(dff.index.to_list()) < hurnum:
            continue
        elif dff.index.to_list()[-1] == datestamp:
            closeset = dff['close'].to_list()
            closelogset = np.log(np.array(closeset)).tolist()
            diffset = pd.Series(closelogset).pct_change().to_list()
            setexcnan = np.array(diffset)[np.logical_not(np.isnan(np.array(diffset)))]
            abswholeset = np.abs(setexcnan)
            close = closeset[-hurnum:]
            clear_close = np.array(close)[np.logical_not(np.isnan(np.array(close)))]
            if len(clear_close) <= int(hurnum*0.9):
                continue
            else:
                lnclose = np.log(np.array(clear_close))
                diff = pd.Series(lnclose.tolist()).pct_change().to_list()[1:]
                absdiff = np.abs(np.array(diff))
                currentdif = np.mean(absdiff)
                countlist = abswholeset[abswholeset <= currentdif].tolist()
                countdif = len(countlist)
                samplelen = len(abswholeset)
                diflvl = (countdif / samplelen) * 100  # 这里比较的是主力合约内部，20日内平均收益率波动在合约历史中的位置

                hurstx = calc_hurst(clear_close.tolist())

        else:
            continue

        if hurstx:
            hurst = round((hurstx * 100), 2)
            resultdict[symb] = [diflvl, hurst]
            diflvlist.append(diflvl)
            hurlist.append(hurst)
            symlist.append(symb)
        else:
            pass

    scatter = (
        Scatter()
        .add_xaxis(hurlist)
        .add_yaxis('V-Level', [list(z) for z in zip(diflvlist, symlist, hurlist)])
        .set_global_opts(title_opts=opts.TitleOpts(title="前{}日收益率波动性描述".format(hurnum), subtitle=datestamp),
                         xaxis_opts=opts.AxisOpts(is_show=False, name='HurstIndex', name_location='center',
                                                  is_scale=True, min_=0, max_=100, interval=20,
                                                  axisline_opts=opts.AxisLineOpts(is_on_zero=True)),
                         yaxis_opts=opts.AxisOpts(is_scale=True, min_=0, max_=100, interval=20,
                                                  splitline_opts=opts.SplitLineOpts(is_show=True)),
                         toolbox_opts=opts.ToolboxOpts(),
                         tooltip_opts=opts.TooltipOpts(
                             formatter=utils.JsCode(
                                 "function(params){return params.value[2]+ ':' +params.value[3]+ '/' +params.value[1];}"
                             )))
        .set_series_opts(label_opts=opts.LabelOpts(
            formatter=utils.JsCode("function (params) {return params.value[2];}")),
            markline_opts=opts.MarkLineOpts(is_silent=True, data=[opts.MarkLineItem(name='HurstLine', x=50)]))
    )
    scatter.width = '600px'
    scatter.height = '600px'
    return scatter


def desc_table(throwdf, skewnum, datestamp):

    # throwdict = {'name': name, 'ttre': totalreturn, 'flcrange': flucrange}
    trdf = throwdf.sort_values('ttre', ascending=False)
    fldf = throwdf.sort_values('flcrange', ascending=False)
    ttre_pos_list, ttre_neg_list, flposlist, flneglist = ["总收益率正向排名"], ["总收益率负向排名"], \
                                                         ["区间振幅正向排名"], ["区间振幅负向排名"]
    for i in range(5):
        ttre_positive = str(trdf.iloc[i]['name']) + ':' + str(trdf.iloc[i]['ttre'])
        ttre_negtive = str(trdf.iloc[-i-1]['name']) + ':' + str(trdf.iloc[-i-1]['ttre'])
        ttre_pos_list.append(ttre_positive)
        ttre_neg_list.append(ttre_negtive)
        fl_pos = str(fldf.iloc[i]['name']) + ':' + str(fldf.iloc[i]['flcrange'])
        fl_neg = str(fldf.iloc[-i-1]['name']) + ':' + str(fldf.iloc[-i-1]['flcrange'])
        flposlist.append(fl_pos)
        flneglist.append(fl_neg)

    table = Table()
    headers = ["描述项目", "第一名", "第二名", "第三名", "第四名", "第五名"]
    rows = [
        ttre_pos_list,
        ttre_neg_list,
        flposlist,
        flneglist
    ]

    table.add(headers, rows).set_global_opts(
        title_opts=opts.ComponentTitleOpts(title="前{}日收益及振幅统计".format(skewnum), subtitle=datestamp)
    )
    return table


def oi_level(namelist, datestamp):
    '''获取当天持仓在历史持仓中的位置'''
    showlist_1 = []
    showlist_2 = []
    for n in namelist:
        spec = Spec(n)
        symb = spec.symbol
        df = read_sql_fut_index('fut_index_{}'.format(symb.lower()))
        if df.empty:
            continue
        elif df.index.to_list()[-1] == datestamp:
            oi_list = df['open_interest'].to_list()
            current_oi = oi_list[-1] 
            lesslist = np.array(oi_list)[np.array(oi_list) <= current_oi].tolist()
            currentlvl = (len(lesslist) / len(oi_list)) * 100
            if currentlvl >= 95:
                showlist_1.append(n)
            elif 85 <= currentlvl < 95:
                showlist_2.append(n)
    return showlist_1, showlist_2


def basis_level(namelist, datestamp):
    '''获取当天基差率在历史基差率中的位置'''
    dstamp = '-'.join([datestamp[0:4], datestamp[4: 6], datestamp[6:]])
    highlist, lowlist = [], []
    for n in namelist:
        spec = Spec(n)
        symbol = spec.symbol
        df = read_sql_spot("spot_{}".format(symbol.lower()))
        if df.empty:
            continue
        elif df.index.to_list()[-1] == dstamp:
            basislist = df['basis'].to_list()
            spot = df['spot'].to_list()
            np.seterr(divide='ignore', invalid='ignore')
            bratelist = np.divide(np.array(basislist), np.array(spot)).tolist()
            current_basis = bratelist[-1]
            lesslist = np.array(bratelist)[np.array(bratelist) <= current_basis].tolist()
            currentlvl = (len(lesslist) / len(bratelist)) * 100
            if currentlvl >= 85:
                highlist.append(n)
            elif currentlvl <= 15:
                lowlist.append(n)
    return highlist, lowlist


def p_level(namelist, datestamp):
    '''现货价格在历史价格中的位置'''
    dstamp = '-'.join([datestamp[0:4], datestamp[4: 6], datestamp[6:]])
    high, low = [], []
    for n in namelist:
        spec = Spec(n)
        symbol = spec.symbol
        df = read_sql_spot("spot_{}".format(symbol.lower()))
        if df.empty:
            continue
        elif df.index.to_list()[-1] == dstamp:
            spot = df['spot'].to_list()
            current = spot[-1]
            lesslist = np.array(spot)[np.array(spot) <= current].tolist()
            currentlvl = (len(lesslist) / len(spot)) * 100
            if currentlvl >= 85:
                high.append(n)
            elif currentlvl <= 15:
                low.append(n)
    return high, low


def fp_level(namelist, datestamp):
    '''期货指数价格在历史价格中的位置'''
    # dstamp = '-'.join([datestamp[0:4], datestamp[4: 6], datestamp[6:]])
    high, low = [], []
    nlist, lvlist, plvlist = [], [], []
    for n in namelist:
        spec = Spec(n)
        symbol = spec.symbol
        df = read_sql_fut_index("fut_index_{}".format(symbol.lower()))
        if df.empty:
            continue
        elif df.index.to_list()[-1] == datestamp:
            fut_p = df['close'].to_list()
            current = fut_p[-1]
            maxp = max(fut_p)
            minp = min(fut_p)
            lesslist = np.array(fut_p)[np.array(fut_p) <= current].tolist()
            currentlvl = round((len(lesslist) / len(fut_p)) * 100, 0)
            plvl = round(((current - minp) / (maxp - minp)) * 100, 0)
            nlist.append(n)
            lvlist.append(currentlvl)
            plvlist.append(plvl)
            if currentlvl >= 85:
                high.append(n)
            elif currentlvl <= 15:
                low.append(n)
    lvdf = pd.DataFrame(data=[lvlist, plvlist], columns=nlist).T
    lvdf.columns = ['lvlist', 'plvlist']
    lvdf.sort_values(by='plvlist', inplace=True)
    bar = (
        Bar()
        .add_xaxis(lvdf.index.to_list())
        .add_yaxis("线性分位", lvdf['plvlist'].to_list())
        .add_yaxis("价格数量分位", lvdf['lvlist'].to_list())
        .set_global_opts(title_opts=opts.TitleOpts(title="指数价格历史分位",
                                                   title_textstyle_opts=opts.TextStyleOpts(font_weight='bold'),
                                                   subtitle="{}".format(datestamp)),
                         yaxis_opts=opts.AxisOpts(max_=100, min_=0), toolbox_opts=opts.ToolboxOpts(),
                         xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=-45)),
                         tooltip_opts=opts.TooltipOpts(trigger='axis'))
        .set_series_opts(label_opts=opts.LabelOpts(is_show=False))
    )
    bar.width = '1600px'
    bar.height = '360px'
    return high, low, bar


def ry_level(namelist, datestamp):
    '''展期收益率在历史价格中的位置'''
    dstamp = '-'.join([datestamp[0:4], datestamp[4: 6], datestamp[6:]])
    high, low = [], []
    for n in namelist:
        spec = Spec(n)
        symbol = spec.symbol
        df = read_sql_ry("roll_yield_{}".format(symbol.lower()))
        if df.empty:
            continue
        elif df.index.to_list()[-1] == dstamp:
            ry = df['roll_yield'].to_list()
            current = ry[-1]
            lesslist = np.array(ry)[np.array(ry) <= current].tolist()
            currentlvl = (len(lesslist) / len(ry)) * 100
            if currentlvl >= 85:
                high.append(n)
            elif currentlvl <= 15:
                low.append(n)
    return high, low


def des_table_2(namelist, datestamp):
    rlist1, rlist2 = oi_level(namelist, datestamp)
    rlist3, rlist4 = basis_level(namelist, datestamp)
    rlist5, rlist6 = p_level(namelist, datestamp)
    rlist7, rlist8, bar = fp_level(namelist, datestamp)
    rlist9, rlist10 = ry_level(namelist, datestamp)
    list1 = ', '.join(rlist1)
    list2 = ', '.join(rlist2)
    list3 = ', '.join(rlist3)
    list4 = ', '.join(rlist4)
    list5 = ', '.join(rlist5)
    list6 = ', '.join(rlist6)
    list7 = ', '.join(rlist7)
    list8 = ', '.join(rlist8)
    list9 = ', '.join(rlist9)
    list10 = ', '.join(rlist10)
    row1 = '持仓处于历史高位(>=95%)'
    row2 = '持仓处于历史较高位(85%~95%)'
    row3 = '基差率处于历史高位(>=85%)'
    row4 = '基差率处于历史低位(<=15%)'
    row9 = '展期收益率处于历史高位(>=85%)'
    row10 = '展期收益率处于历史低位(<=15%)'
    row5 = '现货价格处于历史高位(>=85%)'
    row6 = '现货价格处于历史低位(<=15%)'
    row7 = '期货指数价格处于历史高位(>=85%)'
    row8 = '期货指数价格处于历史低位(<=15%)'
    headers = ['观测项目', '观测结果']
    rows = [
        [row1, list1],
        [row2, list2],
        [row3, list3],
        [row4, list4],
        [row9, list9],
        [row10, list10],
        [row5, list5],
        [row6, list6],
        [row7, list7],
        [row8, list8]
    ]
    table = Table()
    table.add(headers, rows).set_global_opts(
        title_opts=opts.ComponentTitleOpts(title="观测项结果", subtitle="{}".format(datestamp))
    )
    return table, bar


def arbi_table1():
    df = pd.read_csv('ratioup.csv', encoding='gbk')
    namelist = ['套利对'] + df['name'].to_list()
    cur_v = ['当前值'] + df['cur_value'].to_list()
    pos = ['历史水平'] + df['position'].to_list()
    corr = ['相关系数'] + df['corr'].to_list()
    rows = [
        cur_v,
        pos,
        corr
    ]
    table = Table()
    table.add(namelist, rows).set_global_opts(
        title_opts=opts.ComponentTitleOpts(title="套利观察", subtitle='比价高位(>=85%)')
    )
    return table


def arbi_table2():
    df = pd.read_csv('ratiodown.csv', encoding='gbk')
    namelist = ['套利对'] + df['name'].to_list()
    cur_v = ['当前值'] + df['cur_value'].to_list()
    pos = ['历史水平'] + df['position'].to_list()
    corr = ['相关系数'] + df['corr'].to_list()
    rows = [
        cur_v,
        pos,
        corr
    ]
    table = Table()
    table.add(namelist, rows).set_global_opts(
        title_opts=opts.ComponentTitleOpts(title="套利观察", subtitle='比价低位(<=15%)')
    )
    return table


def arbi_table3():
    df = pd.read_csv('difup.csv', encoding='gbk')
    namelist = ['套利对'] + df['name'].to_list()
    cur_v = ['当前值'] + df['cur_value'].to_list()
    pos = ['历史水平'] + df['position'].to_list()
    corr = ['相关系数'] + df['corr'].to_list()
    rows = [
        cur_v,
        pos,
        corr
    ]
    table = Table()
    table.add(namelist, rows).set_global_opts(
        title_opts=opts.ComponentTitleOpts(title="套利观察", subtitle='价差高位(>=85%)')
    )
    return table


def arbi_table4():
    df = pd.read_csv('difdown.csv', encoding='gbk')
    namelist = ['套利对'] + df['name'].to_list()
    cur_v = ['当前值'] + df['cur_value'].to_list()
    pos = ['历史水平'] + df['position'].to_list()
    corr = ['相关系数'] + df['corr'].to_list()
    rows = [
        cur_v,
        pos,
        corr
    ]
    table = Table()
    table.add(namelist, rows).set_global_opts(
        title_opts=opts.ComponentTitleOpts(title="套利观察", subtitle='价差低位(<=15%)')
    )
    return table


def heat_map_futi(namelist):
    #  相关性描述
    try:
        datalist, nlist = [], []
        for name in namelist:
            spec = Spec(name)
            sym = spec.symbol
            symbol = sym.lower()
            df = read_sql_fut_index("fut_index_{}".format(symbol))
            try:
                close = df['close'].to_list()
            except:
                continue
            else:
                datalist.append(close)
                nlist.append(sym)
        df_hm = pd.DataFrame(data=datalist, index=nlist).T
        cor_raw = df_hm.corr()
        values_raw = cor_raw.values.tolist()
        values_new = np.multiply(values_raw, 100)
        cor = pd.DataFrame(data=values_new, index=cor_raw.index.to_list(), columns=cor_raw.columns.to_list())
        corl = len(cor.columns.to_list())
        cordata = [[x, y, cor.iloc[x, y]] for x in range(corl) for y in range(corl)]
    except:
        htmp = (
            HeatMap()
        )
    else:
        htmp = (
            HeatMap()
            .add_xaxis(cor.columns.to_list())
            .add_yaxis('spec', cor.index.to_list(), cordata)
            .set_global_opts(title_opts=opts.TitleOpts(title='品种间相关性描述', subtitle='期货指数'),
                             toolbox_opts=opts.ToolboxOpts(),
                             tooltip_opts=opts.TooltipOpts(trigger='item'),
                             xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=-90)),
                             visualmap_opts=opts.VisualMapOpts(max_=100, min_=-100))
        )
    htmp.width = '900px'
    htmp.height = '900px'
    return htmp


def heat_map_spot(namelist):
    #  相关性描述
    try:
        datalist, nlist = [], []
        for name in namelist:
            spec = Spec(name)
            sym = spec.symbol
            symbol = sym.lower()
            df = read_sql_spot("spot_{}".format(symbol))
            try:
                close = df['spot'].to_list()
            except:
                continue
            else:
                datalist.append(close)
                nlist.append(sym)
        df_hm = pd.DataFrame(data=datalist, index=nlist).T
        cor_raw = df_hm.corr()
        values_raw = cor_raw.values.tolist()
        values_new = np.multiply(values_raw, 100)
        cor = pd.DataFrame(data=values_new, index=cor_raw.index.to_list(), columns=cor_raw.columns.to_list())
        corl = len(cor.columns.to_list())
        cordata = [[x, y, cor.iloc[x, y]] for x in range(corl) for y in range(corl)]
    except:
        htmp = (
            HeatMap()
        )
    else:
        htmp = (
            HeatMap()
            .add_xaxis(cor.columns.to_list())
            .add_yaxis('spec', cor.index.to_list(), cordata)
            .set_global_opts(title_opts=opts.TitleOpts(title='品种间相关性描述', subtitle='现货'),
                             toolbox_opts=opts.ToolboxOpts(),
                             tooltip_opts=opts.TooltipOpts(trigger='item'),
                             xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=-90)),
                             visualmap_opts=opts.VisualMapOpts(max_=100, min_=-100))
        )
    htmp.width = '900px'
    htmp.height = '900px'
    return htmp


def run_indicator(namelist, num):
    page = Page(page_title='交易指标', layout=Page.SimplePageLayout)
    maxi = int(-1 * (num+1))
    arbi1, arbi2, arbi3, arbi4 = arbi_table1(), arbi_table2(), arbi_table3(), arbi_table4()
    heatmapfuti = heat_map_futi(namelist)
    heatmapspot = heat_map_spot(namelist)
    volidex = nh_volitility_index(namelist)
    for i in range(-1, maxi, -1):
        bar_sentiment, date = get_today_sentiment(namelist, i)
        bar_hld1 = get_hld_bar(namelist, date, cat=0)
        bar_hld2 = get_hld_bar(namelist, date, cat=1)
        skew_scatter, throwdf, skewnum = run_skew(namelist, date)
        stattable = desc_table(throwdf, skewnum, date)
        hurst_scatter = run_hurst(namelist, date)
        destable2, indexlvlbar = des_table_2(namelist, date)
        yaf1, yafdf1 = gator_sensor('永安期货', namelist, date, cat=0)
        ydf1, ydfdf1= gator_sensor('一德期货', namelist, date, cat=0)
        htf1, htfdf1 = gator_sensor('华泰期货', namelist, date, cat=0)
        citic1, citicdf1= gator_sensor('中信期货', namelist, date, cat=0)
        gtf1, gtfdf1 = gator_sensor('银河期货', namelist, date, cat=0)
        zlf1, zlfdf1 = gator_sensor('中粮期货', namelist, date, cat=0)
        hatf1, hatfdf1 = gator_sensor('海通期货', namelist, date, cat=0)
        lzf1, lzfdf1 = gator_sensor('鲁证期货', namelist, date, cat=0)
        gjf1, gjfdf1 = gator_sensor('国泰君安', namelist, date, cat=0)
        glist1 = [yafdf1, ydfdf1, htfdf1, citicdf1, gtfdf1, zlfdf1, hatfdf1, lzfdf1, gjfdf1]
        forest1 = gator_forest(glist1, cat=0)
        yaf2, yafdf2 = gator_sensor('永安期货', namelist, date, cat=1)
        ydf2, ydfdf2 = gator_sensor('一德期货', namelist, date, cat=1)
        htf2, htfdf2 = gator_sensor('华泰期货', namelist, date, cat=1)
        citic2, citicdf2 = gator_sensor('中信期货', namelist, date, cat=1)
        gtf2, gtfdf2 = gator_sensor('银河期货', namelist, date, cat=1)
        zlf2, zlfdf2 = gator_sensor('中粮期货', namelist, date, cat=1)
        hatf2, hatfdf2 = gator_sensor('海通期货', namelist, date, cat=1)
        lzf2, lzfdf2 = gator_sensor('鲁证期货', namelist, date, cat=1)
        gjf2, gjfdf2 = gator_sensor('国泰君安', namelist, date, cat=1)
        glist2 = [yafdf2, ydfdf2, htfdf2, citicdf2, gtfdf2, zlfdf2, hatfdf2, lzfdf2, gjfdf2]
        forest2 = gator_forest(glist2, cat=1)
        gatormap = gator_map(forest1, forest2, date)
        separator = draw_separator(date)
        page.add(separator,
                 skew_scatter,
                 hurst_scatter,
                 stattable,
                 destable2,
                 indexlvlbar,
                 volidex,
                 bar_sentiment,
                 arbi1,
                 arbi2,
                 arbi3,
                 arbi4,
                 gatormap,
                 bar_hld1,
                 yaf1,
                 ydf1,
                 htf1,
                 citic1,
                 gtf1,
                 zlf1,
                 hatf1,
                 lzf1,
                 gjf1,
                 bar_hld2,
                 yaf2,
                 ydf2,
                 htf2,
                 citic2,
                 gtf2,
                 zlf2,
                 hatf2,
                 lzf2,
                 gjf2,
                 heatmapfuti,
                 heatmapspot
                 )
    page.render('交易指标.html')

